/*
* CourseDaoImpl.java
* Created on  2014-4-19 下午5:13
* 版本       修改时间          作者      修改内容
* V1.0.1    2014-4-19       gaoxinyu    初始版本
*
*/
package com.genghis.ptas.course.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.core.page.entity.Page;
import com.genghis.core.page.entity.PageParameter;
import com.genghis.ptas.course.dao.CourseDao;
import com.genghis.ptas.course.entity.ChapterInfo;
import com.genghis.ptas.course.entity.CourseComment;
import com.genghis.ptas.course.entity.CourseInfo;
import com.genghis.ptas.course.entity.CourseRegister;
import com.genghis.ptas.security.entity.User;
import com.genghis.ptas.util.DateUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.Calendar;
import java.util.List;

/**
 * 类的描述信息
 *
 * @author gaoxinyu
 * @version 1.0.1
 */
@Repository("courseDao")
public class CourseDaoImpl extends PageDao implements CourseDao {

    private StringBuilder getCourseSql() {
        String userSql = "SELECT c.*, su.user_name AS up_user_name, cs.average_score \n" +
                "FROM course c LEFT JOIN sys_user su ON c.`up_user_id` = su.`id` \n" +
                "LEFT JOIN course_statistics cs ON c.`id` = cs.`course_id` WHERE 1=1";
        return new StringBuilder(userSql);
    }

    private StringBuilder getUserRegisterSql(int userId) {
        return new StringBuilder("SELECT * FROM course_register WHERE student_id=" + userId);
    }

    @Override
    public Page<CourseInfo> findCoursePageList(PageParameter pageParameter, CourseInfo courseInfo) {
        StringBuilder sql = getCourseSql();
        if (!"".equals(courseInfo.getCourseName())) {
            sql.append(" AND c.course_name like '%").append(courseInfo.getCourseName().trim()).append("%'");
        }
        if (courseInfo.getChapterNum() != null) {
            sql.append(" AND c.chapter_num = '").append(courseInfo.getChapterNum()).append("'");
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(courseInfo), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class));
    }

    @Override
    public int addCourse(CourseInfo courseInfo) {
        String sql = "INSERT INTO course(course_name, chapter_num, up_date, state, course_type, photo_url, up_user_id) " +
                "VALUES(:courseName, :chapterNum, :upDate, :state, :courseType, :photoUrl, :upUserId)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseInfo), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public int addChapter(ChapterInfo chapterInfo) {
        String sql = "INSERT INTO course_chapter(chapter_name, chapter_url, course_id, xml_chapter_id, chapter_type, parent_id) " +
                "VALUES(:chapterName, :chapterUrl, :courseId, :xmlChapterId, :chapterType, :parentId)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(chapterInfo), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public void editCourse(CourseInfo courseInfo) {
        String sql = "UPDATE course SET course_name = :courseName, chapter_num = :chapterNum, up_date = :upDate, state = :state , " +
                " course_type = :courseType, photo_url = :photoUrl WHERE id = :id ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseInfo));
    }

    @Override
    public List<ChapterInfo> findChapterByCourseId(Integer id) {
        String sql = "select * from course_chapter where course_id = ?";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<ChapterInfo>(ChapterInfo.class), id);
    }

    @Override
    public CourseInfo findCourseByCourseId(Integer id) {
        String sql = "SELECT c.*,cs.average_score,cs.total_time,cs.collect_number,cs.register_number,cs.total_number,cs.quality,su.user_name AS up_user_name " +
                "FROM course c LEFT JOIN course_statistics cs ON cs.`course_id` = c.`id` LEFT JOIN sys_user su ON c.up_user_id = su.id where c.id = ?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class), id);
    }

    @Override
    public void deleteCourseInfoById(Integer id) {
        String sql = "DELETE FROM course WHERE id = ?";
        getJdbcTemplate().update(sql, id);
    }

    @Override
    public void changeAuditState(CourseInfo courseInfo) {
        String sql = "UPDATE course SET state=:state WHERE id=:id";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseInfo));
    }

    @Override
    public List<CourseInfo> findCourseList(int userId) {
        StringBuilder sql = new StringBuilder("SELECT al.*,us.student_id showRegister FROM ( ");
        sql.append(getCourseSql()).append(" AND state = 1 ) al LEFT JOIN ( ").append(getUserRegisterSql(userId)).append(" ) us ON al.id=us.course_id ");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class));
    }

    @Override
    public int addCourseComment(CourseComment courseComment) {
        String sql = "INSERT INTO course_comment(comment_content, user_id, course_id, comment_date) " +
                "VALUES(:commentContent, :userId, :courseId, :commentDate)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseComment), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public List<CourseComment> findCourseCommentByCourseId(Integer id) {
        String sql = "select * from course_comment where course_id = ?";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<CourseComment>(CourseComment.class), id);
    }

    @Override
    public List<CourseInfo> findCourseListByTypeIdAndName(Integer typeId, String searchName) {
        StringBuilder sql = getCourseSql();
        sql.append(" AND c.course_type = ?");
        if (!"".equals(searchName) && null != searchName) {
            sql.append(" AND c.course_name LIKE '%").append(searchName).append("%'");
        }
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class), typeId);
    }

    @Override
    public List<CourseInfo> findCourseListByCourseName(String courseName) {
        StringBuilder sql = getCourseSql();
        sql.append(" AND c.course_name like '%").append(courseName).append("%'");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class));
    }

    @Override
    public List<CourseInfo> findUploadCourseListByUserId(Integer userId) {
        StringBuilder sql = getCourseSql();
        sql.append(" AND c.up_user_id = '").append(userId).append("'");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class));
    }

    @Override
    public List<CourseInfo> findTopCollectCourse(int userId) {
        StringBuilder sql = new StringBuilder("");
        sql.append("SELECT c.*,cc.collect_date register_date  FROM course_collect cc,course c WHERE cc.course_id=c.id " +
                "AND cc.collect_date>='").append(DateUtil.getAgoDate(Calendar.WEEK_OF_YEAR, -1)).append("' AND cc.user_id=? ORDER BY cc.collect_date DESC ");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class), userId);
    }

    @Override
    public List<CourseInfo> findTopRegisterCourse(int userId) {
        StringBuilder sql = new StringBuilder("");
        sql.append("SELECT c.*,cr.register_date FROM course_register cr,course c WHERE cr.course_id=c.id " +
                "AND cr.register_date>='").append(DateUtil.getAgoDate(Calendar.WEEK_OF_YEAR, -1)).append("' AND cr.student_id=? ORDER BY register_date DESC ");
        return getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<CourseInfo>(CourseInfo.class), userId);
    }

    @Override
    public List<CourseRegister> findRegisterThisMonth(User user) {
        String sql = "SELECT * FROM `course_register` WHERE `student_id` = ? AND `register_date`>'" + DateUtil.getAgoDate(Calendar.MONTH, -1) + "'";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<CourseRegister>(CourseRegister.class), user.getId());
    }


}
